﻿using Microsoft.Data.SqlClient;
using ORMDemo.Core;
using ORMDemo.Core.DBFilter;
using ORMDemo.Core.Mapping;
using ORMDemo.Core.Validate;
using ORMDemo.Dal.ExpressionExtend;
using ORMDemo.Model;
using System.Linq.Expressions;
using System.Reflection;
using System.Reflection.Metadata;

namespace ORMDemo.Dal
{
    public class SqlHelper
    {

        /// <summary>
        /// 根据ID查找返回实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public T Find<T>(int id) where T : BaseModel, new()
        {
            Type type = typeof(T);
            string sql = SqlCacheBuilder<T>.GetSql(SqlCacheBuilderType.FindOne);
            SqlParameter[] sqlParameter = new SqlParameter[] {
                new SqlParameter("@Id",id)
            };

            return ExecuteSQL<T>(sql, sqlParameter, SQLConnectionStringPool.DBOperateType.Read, cmd =>
            {
                var reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    T t = Activator.CreateInstance<T>();
                    foreach (var prop in type.GetProperties())
                    {
                        prop.SetValue(t, reader[prop.GetMappingColumnName()] is DBNull ? null : reader[prop.GetMappingColumnName()]);
                    }
                    return t;
                }
                else
                {
                    return null;
                }
            });
            return null;
        }
        /// <summary>
        /// 插入数据返回是否成功
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public bool Insert<T>(T t) where T : BaseModel, new()
        {
            Type type = typeof(T);
            if (!t.ValidateModel<T>())
            {
                throw new Exception("类型错误");
            }
            string sql = SqlCacheBuilder<T>.GetSql(SqlCacheBuilderType.InsertOne);
            var paramter = type.GetPropertiesWithNokey().Select(p => new SqlParameter($"@{p.GetMappingName()}", $"{p.GetValue(t) ?? DBNull.Value}")).ToArray();
            return ExecuteSQL<bool>(sql, paramter, SQLConnectionStringPool.DBOperateType.Write, cmd => 1 == cmd.ExecuteNonQuery());
        }
        /// <summary>
        /// 更新数据返回是否成功
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public bool Update<T>(T t) where T : BaseModel, new()
        {
            Type type = typeof(T);
            if (!t.ValidateModel<T>())
            {
                throw new Exception("类型错误");
            }
            string sql = SqlCacheBuilder<T>.GetSql(SqlCacheBuilderType.UpdateOne);
            var paramter = type.GetProperties().Select(p => new SqlParameter($"@{p.GetMappingName()}", $"{p.GetValue(t) ?? DBNull.Value}")).ToArray();
            return ExecuteSQL<bool>(sql, paramter, SQLConnectionStringPool.DBOperateType.Write, cmd => 1 == cmd.ExecuteNonQuery());
        }
        /// <summary>
        /// 删除数据返回是否成功
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public bool Delete<T>(T t) where T : BaseModel, new()
        {
            Type type = typeof(T);
            string sql = SqlCacheBuilder<T>.GetSql(SqlCacheBuilderType.DeleteOne);
            PropertyInfo primaryKey = type.GetPropertiesWithkey();
            SqlParameter[] sqlParameter = new SqlParameter[] { new SqlParameter($"@{primaryKey.Name}", $"{primaryKey.GetValue(t) ?? DBNull.Value}") };
            return ExecuteSQL<bool>(sql, sqlParameter, SQLConnectionStringPool.DBOperateType.Write, cmd => 1 == cmd.ExecuteNonQuery());
        }
        /// <summary>
        /// 根据表达式条件批量删除返回是否成功
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expression"></param>
        /// <returns></returns>
        public bool DeleteByParam<T>(Expression<Func<T, bool>> expression) where T : BaseModel, new()
        {
            CustomExpressionVisitor visitor = new CustomExpressionVisitor();
            {
                visitor.Visit(expression);
                string where = visitor.GetWhere();
            }
            //Type type = typeof(T);
            //string sql = SqlCacheBuilder<T>.GetSql(SqlCacheBuilderType.DeleteOne);
            //PropertyInfo primaryKey = type.GetPropertiesWithkey();
            //SqlParameter[] sqlParameter = new SqlParameter[] { new SqlParameter($"@{primaryKey.Name}", $"{primaryKey.GetValue(t) ?? DBNull.Value}") };
            //return ExecuteSQL<bool>(sql, sqlParameter, cmd => 1 == cmd.ExecuteNonQuery());
            return false;
        }
        private S ExecuteSQL<S>(string sql, SqlParameter[] sqlParam, SQLConnectionStringPool.DBOperateType operateType, Func<SqlCommand, S> func)
        {
            string connection = SQLConnectionStringPool.GetConnectionString(operateType);
            using (SqlConnection conn = new SqlConnection(connection))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(sqlParam);
                conn.Open();
                return func.Invoke(cmd);
            }
        }
    }
}
